package com.inspinia.base.util;


import com.inspinia.base.common.model.BaseConstants;
import org.apache.commons.io.FilenameUtils;
import org.apache.commons.lang3.time.FastDateFormat;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

/**
 * Created by jcq on 2016/5/24.
 * 适应小数据量，且直接返回流数据
 */
public abstract class ExportReport {
    private static SimpleDateFormat dateFormat = new SimpleDateFormat(BaseConstants.DATE_TIME_PATTERN);

    /**
     * 导出Excel--反射数据
     *
     * @param fileName 文件名称
     * @param titles   标题列表
     * @param attrs    属性列表
     * @param objs
     * @param response
     */
    public static void exportExcel(String fileName, String[] titles, String[] attrs, List objs, HttpServletResponse response) {
        
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = createSheet(fileName, workbook, titles);
        
        HSSFCellStyle style = ExcelUtils.getStyle(workbook);
        createContent(sheet, objs, attrs, style);
        createExcel(fileName, response, workbook);
    }

    /**
     * 定制导出Excel--反射数据
     *
     * @param fileName
     * @param titles
     * @param attrs
     * @param objs
     * @param response
     */
    public static void exportCustomExcel(String fileName, String[] titles, String[] attrs, List objs, HttpServletResponse response) {
        
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = createSheet(fileName, workbook, titles);
        
        HSSFCellStyle style = ExcelUtils.getStyle(workbook);
        createCustomContent(sheet, objs, attrs, style);
        createExcel(fileName, response, workbook);
    }

    public static void export2010Excel(String fileName, String[] titles, String[] attrs, List objs, HttpServletResponse response) {
        XSSFWorkbook workbook = genXSSFWorkbook(fileName, titles, objs, attrs);
        create2010Excel(fileName, response, workbook);
    }

    /**
     * 导出Excel--反射数据
     *
     * @param fileName 文件名称
     * @param titles   标题列表
     * @param attrs    属性列表
     * @param objs
     */
    public static void exportExcel(String fileName, String[] titles, String[] attrs, List objs) {
        HSSFWorkbook workbook = genWorkbook(FilenameUtils.getName(fileName), titles, objs, attrs);
        createExcel(fileName, workbook);
    }

    private static HSSFWorkbook genWorkbook(String fileName, String[] titles, List objs, String[] attrs) {
        
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = createSheet(fileName, workbook, titles);
        
        HSSFCellStyle style = ExcelUtils.getStyle(workbook);
        createContent(sheet, objs, attrs, style);
        return workbook;
    }

    /**
     * 保存excel到本地
     *
     * @param fileName 文件的绝对路径
     * @param titles
     * @param attrs
     * @param objs
     * @throws IOException
     */
    public static File saveExcel(String filePath, String fileName, String[] titles, String[] attrs, List objs) throws IOException {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = createSheet(fileName, workbook, titles);
        HSSFCellStyle style = ExcelUtils.getStyle(workbook);
        createContent(sheet, objs, attrs, style);
        String name = filePath + File.separator + fileName + ExcelFileSuffix.xls.getName();
        FileUtil.createFolder(name);
        File file = new File(name);
        workbook.write(new FileOutputStream(file));
        return file;
    }

    /**
     * 导出Excel--自定义数据
     *
     * @param fileName
     * @param titles
     * @param data
     * @param response
     */
    public static void exportExcel(String fileName, String[] titles, List<List<Object>> data, HttpServletResponse response) {
        HSSFWorkbook workbook = genWorkbook(fileName, titles, data);
        createExcel(fileName, response, workbook);
    }

    /**
     * 导出Excel--自定义数据
     *
     * @param targetFilePath 目标文件路径（绝对路径）
     * @param titles         标题
     * @param data           数据
     */
    public static void exportExcel(String targetFilePath, String[] titles, List<List<Object>> data) {
        HSSFWorkbook workbook = genWorkbook(FilenameUtils.getName(targetFilePath), titles, data);
        createExcel(targetFilePath, workbook);
    }

    /**
     * 追加数据到excel中
     *
     * @param targetFilePath excel的路径
     * @param titles         标题
     * @param data           数据
     */
    public static void appendData2Excel(String targetFilePath, String[] titles, String[] attr, List data) {
        boolean exist = FileUtil.isExist(targetFilePath);
        if (!exist) {
            //如果文件不存在的时候，就先创建该excel
            exportExcel2010(targetFilePath, titles, attr, data);
        } else {
            InputStream fs = null;
            try {
                fs = new FileInputStream(targetFilePath);
                Workbook wb = new XSSFWorkbook(fs);
                //todo 目前支持一个sheet
                Sheet sheet = wb.getSheetAt(0);
                FileOutputStream out = new FileOutputStream(targetFilePath);

                int rows = data.size();
                CellStyle style = ExcelUtils.getStyle(wb);
                int columns = attr.length;
                int lastRowNum = sheet.getLastRowNum();
                for (int i = 0; i < rows; i++) {
                    //在现有行号后追加数据
                    Row row = sheet.createRow(lastRowNum + i);
                    for (int j = 0; j < columns; j++) {
                        Object value = ExcelUtils.getter(data.get(i), StringUtils.capitalize(attr[j]));
                        createCell(row, j, value, style);
                    }
                }
                out.flush();
                wb.write(out);
                out.close();
            } catch (FileNotFoundException e) {
                throw new RuntimeException(e);
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
        }
    }

    /**
     * 导出2010以上的excel
     *
     * @param targetFilePath
     * @param titles
     * @param attr
     * @param data
     */
    private static void exportExcel2010(String targetFilePath, String[] titles, String[] attr, List data) {
        XSSFWorkbook wb = genXSSFWorkbook(FilenameUtils.getName(targetFilePath), titles, data, attr);
        createExcel(targetFilePath, wb);
    }

    /**
     * 给文件添加导出时候的后缀
     *
     * @param fileName
     * @return
     */
    private static String addFileSuffix(String fileName, ExcelFileSuffix suffix) {
        //todo 其实这样并不科学，因为服务器时间和用户的时间不是同步的，如果不同步，则用户看到文件时间就不准确
        fileName += FastDateFormat.getInstance(BaseConstants.DATE_TIME_PATTERN).format(System.currentTimeMillis()) + suffix.getName();
        return fileName;
    }


    /**
     * 创建workbook
     *
     * @param fileName
     * @param titles
     * @param data
     * @param attr
     * @return
     */
    private static XSSFWorkbook genXSSFWorkbook(String fileName, String[] titles, List data, String[] attr) {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = createXSSFSheet(fileName, workbook, titles);
        CellStyle style = ExcelUtils.getStyle(workbook);
        createContent(sheet, data, attr, style);
        return workbook;
    }

    private static XSSFSheet createXSSFSheet(String fileName, XSSFWorkbook workbook, String[] titles) {
        CellStyle columnTopStyle = ExcelUtils.getColumnTopStyle(workbook);
        XSSFSheet sheet = workbook.createSheet(fileName);
        createTitle(sheet, titles, columnTopStyle);
        return sheet;
    }

    private static HSSFWorkbook genWorkbook(String fileName, String[] titles, List<List<Object>> data) {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = createSheet(fileName, workbook, titles);
        HSSFCellStyle style = ExcelUtils.getStyle(workbook);
        createContent(sheet, data, style);
        return workbook;
    }

    /**
     * 创建sheet及标题
     *
     * @param fileName
     * @param workbook
     * @param titles
     * @return
     */
    public static HSSFSheet createSheet(String fileName, HSSFWorkbook workbook, String[] titles) {
        HSSFCellStyle titleStyle = ExcelUtils.getColumnTopStyle(workbook);
        HSSFSheet sheet = workbook.createSheet(fileName);
        createTitle(sheet, titles, titleStyle);
        return sheet;
    }


    /**
     * 导出文件
     *
     * @param targetFilePath 写出excel的文件路径
     * @param workbook
     */
    public static void createExcel(String targetFilePath, Workbook workbook) {
        autoWidth(workbook.getSheetAt(0), workbook.getSheetAt(0).getRow(0).getLastCellNum());
        if (StringUtils.isBlank(FileUtil.getSuffix(targetFilePath))) {
            targetFilePath += ExcelFileSuffix.xlsx.getName();
        }
        try {
            FileUtil.create(targetFilePath);
            workbook.write(new FileOutputStream(new File(targetFilePath)));
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }


    /**
     * 导出文件
     *
     * @param fileName
     * @param response
     * @param workbook
     */
    public static void createExcel(String fileName, HttpServletResponse response, Workbook workbook) {
        autoWidth(workbook.getSheetAt(0), workbook.getSheetAt(0).getRow(0).getLastCellNum());
        try {
            workbook.write(ContainerUtil.getDownloadFileOutputStream(response, addFileSuffix(fileName, ExcelFileSuffix.xls)));
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }


    /**
     * 导出2010excel
     *
     * @param fileName
     * @param response
     * @param workbook
     */
    public static void create2010Excel(String fileName, HttpServletResponse response, Workbook workbook) {
        autoWidth(workbook.getSheetAt(0), workbook.getSheetAt(0).getRow(0).getLastCellNum());
        try {
            workbook.write(ContainerUtil.getDownloadFileOutputStream(response, addFileSuffix(fileName, ExcelFileSuffix.xlsx)));
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }


    public static void createTitle(Sheet sheet, String[] titles, CellStyle titleStyle) {
        Row row = sheet.createRow(0);
        int len = titles.length;
        for (int i = 0; i < len; i++) {
            createCell(row, i, titles[i], titleStyle);
        }
    }

    /**
     * 根据数据创建内容
     *
     * @param sheet
     * @param data
     */
    public static void createContent(Sheet sheet, List<List<Object>> data, CellStyle style) {
        int rows = data.size();
        int columns = rows > 0 ? data.get(0).size() : 0;
        for (int i = 0; i < rows; i++) {
            Row row = sheet.createRow(i + 1);
            List<Object> temp = data.get(i);
            for (int j = 0; j < columns; j++) {
                createCell(row, j, temp.get(j), style);
            }
        }
    }

    /**
     * 根据属性，反射出对象值并创建内容
     *
     * @param sheet
     * @param objs
     * @param attrs
     */
    public static void createContent(Sheet sheet, List<Object> objs, String[] attrs, CellStyle style) {
        int rows = objs.size();
        int columns = attrs.length;
        for (int i = 0; i < rows; i++) {
            Row row = sheet.createRow(i + 1);
            System.out.println("正在生成第" + (i + "/" + rows) + "行");
            for (int j = 0; j < columns; j++) {
                Object value = ExcelUtils.getter(objs.get(i), StringUtils.capitalize(attrs[j]));
                if (("ServerIp".equals(attrs[j]) || "CallerIp".equals(attrs[j]) || "CalleeIp".equals(attrs[j])) && value != null) {
                    String s = value.toString();
                    if (StringUtils.isNotBlank(s) && !"null".equals(s)) {
                        Long ip = Long.valueOf(s);
                        if (ip != null) {
                            value = longToIp(ip);
                        }
                    }
                }
                if ("StartTime".equals(attrs[j]) || "StopTime".equals(attrs[j])) {
                    String s = value.toString();
                    if (StringUtils.isNotBlank(s) && !"null".equals(s)) {
                        String date = dateFormat.format(new Date(Long.valueOf(s)));
                        value = date;
                    }
                }
                createCell(row, j, value, style);
            }
        }
    }

    public static void createCustomContent(Sheet sheet, List<Object> objs, String[] attrs, CellStyle style) {
        int rows = objs.size();
        int columns = attrs.length;
        for (int i = 0; i < rows; i++) {
            Row row = sheet.createRow(i + 1);
            for (int j = 0; j < columns; j++) {
                Object value = ExcelUtils.getter(objs.get(i), StringUtils.capitalize(attrs[j]));
                if ("Calleee164".equals(attrs[j]) && value != null) {
                    String str = value.toString();
                    if (StringUtils.isNotBlank(str)) {
                        if (str.length() > 11) {
                            value = str.substring(str.length() - 11, str.length());
                        }
                    }
                }
                if (("ServerIp".equals(attrs[j]) || "CallerIp".equals(attrs[j]) || "CalleeIp".equals(attrs[j])) && value != null) {
                    String str = value.toString();
                    if (StringUtils.isNotBlank(str)) {
                        Long ip = Long.valueOf(str);
                        if (ip != null) {
                            value = longToIp(ip);
                        }
                    }
                }
                if ("StartTime".equals(attrs[j]) || "StopTime".equals(attrs[j])) {
                    String s = value.toString();
                    if (StringUtils.isNotBlank(s) && !"null".equals(s)) {
                        String date = dateFormat.format(new Date(Long.valueOf(s)));
                        value = date;
                    }
                }
                createCell(row, j, value, style);
            }
        }
    }

    /**
     * 创建cell并设置最小宽度
     *
     * @param row
     * @param index
     * @param value
     * @param style
     */
    public static void createCell(Row row, int index, Object value, CellStyle style) {
        Cell cell = row.createCell(index);
        if (value == null || "null".equals(value.toString())) {
            value = "";
        }
        RichTextString str = null;
        if (row instanceof HSSFRow) {
            str = new HSSFRichTextString(String.valueOf(value));
        } else if (row instanceof XSSFRow) {
            str = new XSSFRichTextString(String.valueOf(value));
        }
        cell.setCellValue(str);
        if (style != null)
            cell.setCellStyle(style);
    }

    public static void autoWidth(Sheet sheet, int size) {
        for (int i = 0; i < size; i++) {
            // 调整第一列宽度
            sheet.autoSizeColumn((short) i);
        }
    }

    public static void autoWidth(SXSSFSheet sheet, int size) {
        for (int i = 0; i < size; i++) {
            // 调整第一列宽度
            sheet.autoSizeColumn(i);
        }
    }

    public static String longToIp(long i) {
        return ((i >> 24) & 0xFF) + "." +

                ((i >> 16) & 0xFF) + "." +

                ((i >> 8) & 0xFF) + "." +

                (i & 0xFF);

    }

    /**
     * 调整宽度并导出大数据格式的excel。
     *
     * @param fileName 文件名称
     * @param response
     * @param workbook
     */
    public static void exportSXSSFSheet(String fileName, HttpServletResponse response, SXSSFWorkbook workbook) {
        try {
            workbook.write(ContainerUtil.getDownloadFileOutputStream(response, addFileSuffix(fileName, ExcelFileSuffix.xlsx)));
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }
}
